﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using Telerik.Web.UI;
using System.IO;
using Aspose.Excel;

namespace LenderClearingHouse
{
    public partial class RentProjection : PageBase
    {
    protected void Page_Load(object sender, EventArgs e)
    {
        Session["SourcePage"] = SourcePage.RentProjection;
        listFilter.SearchButton.Click += new ImageClickEventHandler(SearchButton_Click);

        if (!IsPostBack)
        {
            GetConfig();
            SaveSortedPropertyIdsToSession(null);
        }

        if (Session["BackToRentProjection"] != null)
        {
            object sortInfo = Session[GetLastSortingKey()];
            object pageInfo = Session[GetLastPagingKey()];

            if (sortInfo != null)
            {
                string sortField = (sortInfo as string).Split(';')[0];
                string sortOrder = (sortInfo as string).Split(';')[1];

                grid.MasterTableView.SortExpressions.AddSortExpression(
                new GridSortExpression()
                {
                    FieldName = sortField,
                    SortOrder = sortOrder == "Ascending" ? GridSortOrder.Ascending : GridSortOrder.Descending
                });
            }

            if (pageInfo != null)
            {
                grid.CurrentPageIndex = (int)pageInfo;
            }

            Session["BackToRentProjection"] = null;
        }


      /*RadAjaxManager.AjaxSettings.AddAjaxSetting(Grid, Grid);

      this.Grid.ItemEvent += new GridItemEventHandler(Grid_ItemEvent);
      this.Grid.ItemCreated += new GridItemEventHandler(Grid_ItemCreated);*/

      
    }

    void SearchButton_Click(object sender, ImageClickEventArgs e)
    {
        grid.Rebind();
        listFilter.ClearFilter();
    }

    protected void SqlDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        User u = Session["User"] as User;

        if (!string.IsNullOrEmpty(Apn))
            e.Command.Parameters["@Apn"].Value = Apn;
        else
            e.Command.Parameters["@Apn"].Value = listFilter.Apn;
        e.Command.Parameters["@PropLocation"].Value = listFilter.Address;
        e.Command.Parameters["@Zip"].Value = listFilter.Zip;
        if (u.UserRole == UserRole.InvestorUser)
        {
            filterGroup.Visible = false;
            cbInvestor.Visible = false;
            e.Command.Parameters["@UserId"].Value = u.UserId;
        }
        else
        {
            filterGroup.Visible = true;
            cbInvestor.Visible = true;
            if (cbInvestor.SelectedIndex != -1)
                e.Command.Parameters["@UserId"].Value = cbInvestor.SelectedValue;
        }
    }

    protected void grid_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
    {
        GridEditableItem editedItem = e.Item as GridEditableItem;
        if (e.CommandName == "NewUnit")
        {
            Session["AddRentalUnitApn"] = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["Apn"].ToString();
            grid.MasterTableView.IsItemInserted = true;
            grid.Rebind();
        }
    }

    protected void grid_UpdateCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
    {
        GridEditableItem editedItem = e.Item as GridEditableItem;
        RentProjectionEditControl userControl = (RentProjectionEditControl)e.Item.FindControl(GridEditFormItem.EditFormUserControlID);
        
        object investorId = DBNull.Value;
        if (userControl.ddlInvestor.SelectedIndex > 0)
            investorId = userControl.ddlInvestor.SelectedValue;

        DataAccess da = new DataAccess();
        da.ExecuteProcedure("updateRentProjection",
            new string[]
                {
                    "@Apn"
	                ,"@PropertyLocation"
	                ,"@InvestorId"
	                ,"@ZipCode"
	                ,"@PurchasePrice"
	                ,"@ClosingCost"
	                ,"@RentMonth"
	                ,"@HOAMonth"
                    ,"@RenterName"
	                ,"@RenterPhone"
	                ,"@DateRentedFrom"
	                ,"@DateRentedTo"
	                ,"@ManagementCompany"
	                ,"@Contact"
	                ,"@CompanyPhone"
	                ,"@CompanyAddress",
                    "@RentProjectionUnitId", "@UnitNumber", "@SquareFootage", "@Bedrooms", "@Bathrooms", "@Rehab"
                },
            new object[]
                {
	                userControl.edAPN.Text
	                ,userControl.edPropertyLocation.Text
	                ,investorId
	                ,userControl.edZipCode.Text
	                ,userControl.tbPurchasePrice.Value
	                ,userControl.tbClosingCost.Value
                    ,userControl.tbRentMonth.Value
                    ,userControl.tbHOAMonth.Value
                    ,userControl.tbRenterName.Text
                    ,userControl.tbRenterPhone.Text
                    ,userControl.tbDateRentedFrom.DbSelectedDate
                    ,userControl.tbDateRentedTo.DbSelectedDate
                    ,userControl.tbManagementCompany.Text
                    ,userControl.tbContact.Text
                    ,userControl.tbCompanyPhone.Text
                    ,userControl.tbCompanyAddress.Text,

                    userControl.RentProjectionUnitId, userControl.edUnitNumber.Text, userControl.edSquareFootage.DbValue, userControl.edBedrooms.SelectedValue,
                    userControl.edBathrooms.Text, userControl.edRehab.DbValue
                });
    }

    protected void grid_InsertCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
    {
        GridEditableItem editedItem = e.Item as GridEditableItem;
        RentProjectionEditControl userControl = (RentProjectionEditControl)e.Item.FindControl(GridEditFormItem.EditFormUserControlID);

        object investorId = DBNull.Value;
        if (userControl.ddlInvestor.SelectedIndex > 0)
            investorId = userControl.ddlInvestor.SelectedValue;

        DataAccess da = new DataAccess();
        da.ExecuteProcedure("updateRentProjection",
            new string[]
                {
                    "@Apn"
	                ,"@PropertyLocation"
	                ,"@InvestorId"
	                ,"@ZipCode"
	                ,"@PurchasePrice"
	                ,"@ClosingCost"
	                ,"@RentMonth"
	                ,"@HOAMonth"
                    ,"@RenterName"
	                ,"@RenterPhone"
	                ,"@DateRentedFrom"
	                ,"@DateRentedTo"
	                ,"@ManagementCompany"
	                ,"@Contact"
	                ,"@CompanyPhone"
	                ,"@CompanyAddress",
                    "@RentProjectionUnitId", "@UnitNumber", "@SquareFootage", "@Bedrooms", "@Bathrooms", "@Rehab"
                },
            new object[]
                {
	                userControl.edAPN.Text
	                ,userControl.edPropertyLocation.Text
	                ,investorId
	                ,userControl.edZipCode.Text
	                ,userControl.tbPurchasePrice.Value
	                ,userControl.tbClosingCost.Value
                    ,userControl.tbRentMonth.Value
                    ,userControl.tbHOAMonth.Value
                    ,userControl.tbRenterName.Text
                    ,userControl.tbRenterPhone.Text
                    ,userControl.tbDateRentedFrom.DbSelectedDate
                    ,userControl.tbDateRentedTo.DbSelectedDate
                    ,userControl.tbManagementCompany.Text
                    ,userControl.tbContact.Text
                    ,userControl.tbCompanyPhone.Text
                    ,userControl.tbCompanyAddress.Text,

                    userControl.RentProjectionUnitId, userControl.edUnitNumber.Text, userControl.edSquareFootage.DbValue, userControl.edBedrooms.SelectedValue,
                    userControl.edBathrooms.Text, userControl.edRehab.DbValue
                });
    }

    protected void grid_DeleteCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
    {
        GridDataItem itm = e.Item as GridDataItem;
        string apn = (string)(sender as RadGrid).MasterTableView.DataKeyValues[itm.ItemIndex]["Apn"];
        object rentProjectionUnitId = (sender as RadGrid).MasterTableView.DataKeyValues[itm.ItemIndex]["RentProjectionUnitId"];
                
        DataAccess da = new DataAccess();
        da.ExecuteProcedure("deleteRentProjection",
          new string[] { "@Apn", "@RentProjectionUnitId" },
          new object[] { apn, rentProjectionUnitId });
    }    

    protected void grid_ItemDataBound(object sender, GridItemEventArgs e)
    {
        if (e.Item is GridPagerItem)
        {
            GridPagerItem item = e.Item as GridPagerItem;
            RadComboBox combo = (RadComboBox)item.FindControl("PageSizeComboBox");

            RadComboBoxItem newItem = combo.Items.FindItemByValue("100");
            if (newItem == null)
            {
                RadComboBoxItem citem = new RadComboBoxItem("100", "100");
                citem.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
                combo.Items.Add(citem);
            }
            else
            {
                newItem.Text = "100";
            }

            newItem = combo.Items.FindItemByValue("1000000");
            if (newItem == null)
            {
                RadComboBoxItem citem = new RadComboBoxItem("ALL", "1000000");
                citem.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
                combo.Items.Add(citem);
            }
            else
            {
                newItem.Text = "ALL";
            }
        }
    }

    protected void lnkRefreshGrid_Click(object sender, EventArgs e)
    {
        grid.Rebind();
    }

    protected void btnApnAdd_Click(object sender, EventArgs e)
    {
        User u = Session["User"] as User;
        DataAccess da = new DataAccess();
        DataTable tbl = da.GetDataTable("insertRentProjection", new string[] {"@Apn", "@UserId"}, new object[] {tbApnAdd.Text, u.UserId});
        if (Convert.ToInt32(tbl.Rows[0]["IdResult"]) == 0)
        {
            tbApnAdd.Text = "";
            grid.Rebind();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "key", string.Format("alert('There are {0} properties added');", tbl.Rows[0]["Result"].ToString()), true);
        }
        else
            ScriptManager.RegisterStartupScript(this, this.GetType(), "key", string.Format("alert('{0}');", tbl.Rows[0]["Result"].ToString()), true);
    }

    protected void cbInvestor_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
    {
        grid.Rebind();
    }

    private void GetConfig()
    {
        DataAccess da = new DataAccess();
        DataTable tbl = da.ExecuteSQL("select * from Config where [Key] like 'RentProjection_%'");

        foreach (DataRow dr in tbl.Rows)
        {
            if (dr["Key"].ToString() == "RentProjection_PercentF2")
            {
                tbF2.Text = dr["Value"].ToString().Replace(",", ".");
            }
            else if (dr["Key"].ToString() == "RentProjection_PercentK2")
            {
                tbK2.Text = dr["Value"].ToString().Replace(",", ".");
            }
            else if (dr["Key"].ToString() == "RentProjection_PercentL2")
            {
                tbL2.Text = dr["Value"].ToString().Replace(",", ".");
            }
            else if (dr["Key"].ToString() == "RentProjection_PercentM2")
            {
                tbM2.Text = dr["Value"].ToString().Replace(",", ".");
            }
            else if (dr["Key"].ToString() == "RentProjection_PercentN2")
            {
                tbN2.Text = dr["Value"].ToString().Replace(",", ".");
            }
            else if (dr["Key"].ToString() == "RentProjection_PercentP2")
            {
                tbP2.Text = dr["Value"].ToString().Replace(",", ".");
            }
        }

    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        Utilities.SaveConfig("RentProjection_PercentF2", tbF2.Text);
        Utilities.SaveConfig("RentProjection_PercentK2", tbK2.Text);
        Utilities.SaveConfig("RentProjection_PercentL2", tbL2.Text);
        Utilities.SaveConfig("RentProjection_PercentM2", tbM2.Text);
        Utilities.SaveConfig("RentProjection_PercentN2", tbN2.Text);
        Utilities.SaveConfig("RentProjection_PercentP2", tbP2.Text);
        
        grid.Rebind();
    }

    protected void lnkExportToExcel_Click(object sender, EventArgs e)
    {
        ExportToExcel();
    }

    private void ExportToExcel()
    {
        DataView dv = new DataView();
        DataTable dt = new DataTable();
        dv = (DataView)(grid.DataSourceObject as SqlDataSource).Select(DataSourceSelectArguments.Empty);
        dt = dv.ToTable();
        
        DataRow summaryRow = dt.NewRow();
        decimal summa = Convert.ToDecimal(dt.Compute("SUM(PurchasePrice)", ""));
        summaryRow["PurchasePrice"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(ClosingCost)", ""));
        summaryRow["ClosingCost"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(PurchaseTotal)", ""));
        summaryRow["PurchaseTotal"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(RehabCost)", ""));
        summaryRow["RehabCost"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(TotalAcquisition)", ""));
        summaryRow["TotalAcquisition"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(RentMonth)", ""));
        summaryRow["RentMonth"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(TaxesMonth)", ""));
        summaryRow["TaxesMonth"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(AnnualVacancyLossMonth)", ""));
        summaryRow["AnnualVacancyLossMonth"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(MaintenanceMonth)", ""));
        summaryRow["MaintenanceMonth"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(ManagementMonth)", ""));
        summaryRow["ManagementMonth"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(HOAMonth)", ""));
        summaryRow["HOAMonth"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(InsuranceMonth)", ""));
        summaryRow["InsuranceMonth"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(MonthlyExpense)", ""));
        summaryRow["MonthlyExpense"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(MonthlyCashFlowProfit)", ""));
        summaryRow["MonthlyCashFlowProfit"] = summa;
        summa = Convert.ToDecimal(dt.Compute("SUM(AnnualCashFlowProfit)", ""));
        summaryRow["AnnualCashFlowProfit"] = summa;
        dt.Rows.Add(summaryRow);

        License license = new License();
        string path = Server.MapPath("Aspose.Excel.lic");
        license.SetLicense(path);

        Excel ex = new Excel();
        Worksheet ws = ex.Worksheets[0];

        int startRow = 0;
        Utilities.GridToExcel(dt, grid, ws, true, ref startRow);
        for (int i = 1; i <= startRow; ++i)
        {
            ws.Cells[i, 22].Style.Custom = "dd.mm.yyyy";
            ws.Cells[i, 23].Style.Custom = "dd.mm.yyyy";
        }

        ex.Save("RentProjection.xls", FileFormatType.Excel2003, SaveType.OpenInExcel, HttpContext.Current.Response);
        HttpContext.Current.Response.End();
    }

    void SaveSortedPropertyIdsToSession(string sortExpression)
    {
        if (ForceEdit)
            return;

        DataTable tbl = (SqlDataSource.Select(DataSourceSelectArguments.Empty) as DataView).Table;
        List<int> ids = new List<int>();

        if (string.IsNullOrEmpty(sortExpression))
        {
            foreach (DataRow dr in tbl.Rows)
            {
                ids.Add(Convert.ToInt32(dr["PropertyId"]));
            }
        }
        else
        {
            foreach (DataRow dr in tbl.Select("", sortExpression))
            {
                ids.Add(Convert.ToInt32(dr["PropertyId"]));
            }
        }

        Session["AllPropertiesIds"] = ids.ToArray();
    }

    protected void grid_SortCommand(object sender, GridSortCommandEventArgs e)
    {
        string lastSortingKey = GetLastSortingKey();
        Session[lastSortingKey] = e.SortExpression + ";" + e.NewSortOrder.ToString();

        SaveSortedPropertyIdsToSession(e.SortExpression + " "
            + (e.NewSortOrder.ToString().StartsWith("Asc") ? "ASC" : "DESC"));
    }

    protected void grid_PageIndexChanged(object sender, GridPageChangedEventArgs e)
    {
        Session[GetLastPagingKey()] = e.NewPageIndex;
    }

    protected string GetLastSortingKey()
    {
        return string.Format("last_sorting_rentprojection");
    }

    protected string GetLastPagingKey()
    {
        return string.Format("last_paging_rentprojection");
    }

    protected void grid_ItemCreated(object sender, GridItemEventArgs e)
    {
        if (!Page.IsPostBack && e.Item is GridEditableItem && ForceEdit)
        {
            e.Item.Edit = true;
        }
    }
        
    protected string Apn
    {
        get
        {
            string result = "";
            if (Request.QueryString["apn"] != null)
                result = Request.QueryString["apn"].ToString();

            return result;
        }
    }

    protected bool ForceEdit
    {
        get
        {
            return (Request.QueryString["edit"] != null);
        }
    }
  }
}